package uk.co.lunagroup.vladar.oncenter;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;


public class ReadExcel {
	/**
	 * returns Sheet 
	 * @param file file from which data should be taken
	 * @param sheetName name of the sheet where from get data
	 * @param columnName number of columns to copy file 
	 * @return - worksheet as HSSF object
	 */

	public static HSSFSheet openExcelFile(String file, String sheetName, int columnCount) {
		
					
	HSSFSheet worksheet=null;

	try {
		FileInputStream iStream = new FileInputStream(file);
		HSSFWorkbook workbook = new HSSFWorkbook(iStream);
		worksheet = workbook.getSheet(sheetName);
		} catch (FileNotFoundException e) {
	      e.printStackTrace();
		} catch (IOException e) {
	      e.printStackTrace();
		} catch(NullPointerException e) {
	   	  e.printStackTrace();
		}
	    
		
	return worksheet;
}



	/**
	 * returns an array containing data from excel file
	 * @param - file name for the choosen excel file
	 * @return - 2 dimensional array of string
	 */
public static String[][] readData(HSSFSheet worksheet, int columnCount) {
	
	
	//declare 2 dimensional array and initialize this table
	String[][] dataArray = null;
			

	//get the last number of the used rows
	int rowCount = worksheet.getLastRowNum() +1;
	//initialize 2 dimensional array to exact column/rows
	dataArray = new String[columnCount][rowCount];

	for (int i=0; i<rowCount; i++) {

		//get row
		HSSFRow row = worksheet.getRow(i);
	
		for (int j=0; j<columnCount; j++){
			//get cell value
			HSSFCell cell = row.getCell(j, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK); //if there is blank cell create null
			//convert cell values into string
			String cellConvertedValue = convertCellValuesInString(cell);
			//debug
			//System.out.println("column "+j+" row "+i+" value is "+cellConvertedValue); //converting values to string
			dataArray[j][i] = cellConvertedValue;
		}
	}
	
    		
	return dataArray;
	}

/**
 * Convert values from cell into string 
 * @param cell
 * @return cell value as String
 */

public static String convertCellValuesInString (HSSFCell cell){
	//TODO switch to distinquish type of data and change all to String
	String convertedValue="";
	
	int cellType = cell.getCellType();
	if (cellType == Cell.CELL_TYPE_STRING) {
		//convertedValue=cell.getRichStringCellValue().getString();
    		//not required as in previous procedure converting this to string
        convertedValue=cell.getStringCellValue();
	} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
		convertedValue=(String.valueOf(cell.getNumericCellValue()));
	} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
		convertedValue=(String.valueOf(cell.getBooleanCellValue()));
	} else if (cellType == Cell.CELL_TYPE_FORMULA) {
		convertedValue=(String.valueOf(cell.getCellFormula()));
	}
	return convertedValue;
}
}
